﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;
using System.IO;
using System.Web;
using System.Web.UI;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class ResourceMasterItemDAL : IDALResourceMasterItem
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ResourceMasterItemDAL()
        {

        }

        public IList<ResourceMasterItemInfo> GetResourceMasterItem()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<ResourceMasterItemInfo> infoList = new List<ResourceMasterItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT i.*"
                            + ", u.ICM_UNIT_NAME AS UNIT_NAME"
                        + " FROM ICM_MAS_RESOURCE_ITEM i"
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                        + " WHERE RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        ResourceMasterItemInfo info = new ResourceMasterItemInfo();
                        ResourceMasterSubGroupInfo infoSubGroup = new ResourceMasterSubGroupInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_RES_ITEM_SEQ"]);
                        info.Code = (dr[i]["ICM_RES_ITEM_CODE"]).ToString();
                        info.Name = (dr[i]["ICM_RES_ITEM_NAME"]).ToString();
                        info.Descr = (dr[i]["ICM_RES_ITEM_DESCR"]).ToString();
                        info.Gpsc_code = (dr[i]["GPSC_CODE"]).ToString();
                        info.Giin_code = (dr[i]["GIIN_CODE"]).ToString();
                        info.Std_price = (Convert.IsDBNull(dr[i]["STD_PRICE"]) ? null : (Nullable<double>)(dr[i]["STD_PRICE"]));
                        info.Min_price = (Convert.IsDBNull(dr[i]["MIN_PRICE"]) ? null : (Nullable<double>)(dr[i]["MIN_PRICE"]));
                        info.Max_price = (Convert.IsDBNull(dr[i]["MAX_PRICE"]) ? null : (Nullable<double>)(dr[i]["MAX_PRICE"]));
                        info.Min_qty = (Convert.IsDBNull(dr[i]["MIN_QTY"]) ? null : (Nullable<int>)(dr[i]["MIN_QTY"]));
                        info.Max_qty = (Convert.IsDBNull(dr[i]["MAX_QTY"]) ? null : (Nullable<int>)(dr[i]["MAX_QTY"]));
                        info.Image_name = (dr[i]["IMAGE_NAME"]).ToString();
                        infoSubGroup.Seq = (Convert.IsDBNull(dr[i]["ICM_RES_SUB_GRP_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_RES_SUB_GRP_SEQ"]));
                        infoUnit.Seq = (Convert.IsDBNull(dr[i]["ICM_UNIT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_UNIT_SEQ"]));
                        infoUnit.Name = (dr[i]["UNIT_NAME"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoSubGroup = infoSubGroup;
                        info.InfoUnit = infoUnit;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<ResourceMasterItemInfo> GetResourceMasterItem(string record_status)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<ResourceMasterItemInfo> infoList = new List<ResourceMasterItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@record_status", record_status);

                strSQL = "SELECT i.*"
                            + ", u.ICM_UNIT_NAME AS UNIT_NAME"
                        + " FROM ICM_MAS_RESOURCE_ITEM i"
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                        + " WHERE i.RECORD_STATUS = @record_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        ResourceMasterItemInfo info = new ResourceMasterItemInfo();
                        ResourceMasterSubGroupInfo infoSubGroup = new ResourceMasterSubGroupInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_RES_ITEM_SEQ"]);
                        info.Code = (dr[i]["ICM_RES_ITEM_CODE"]).ToString();
                        info.Name = (dr[i]["ICM_RES_ITEM_NAME"]).ToString();
                        info.Descr = (dr[i]["ICM_RES_ITEM_DESCR"]).ToString();
                        info.Gpsc_code = (dr[i]["GPSC_CODE"]).ToString();
                        info.Giin_code = (dr[i]["GIIN_CODE"]).ToString();
                        info.Std_price = (Convert.IsDBNull(dr[i]["STD_PRICE"]) ? null : (Nullable<double>)(dr[i]["STD_PRICE"]));
                        info.Min_price = (Convert.IsDBNull(dr[i]["MIN_PRICE"]) ? null : (Nullable<double>)(dr[i]["MIN_PRICE"]));
                        info.Max_price = (Convert.IsDBNull(dr[i]["MAX_PRICE"]) ? null : (Nullable<double>)(dr[i]["MAX_PRICE"]));
                        info.Min_qty = (Convert.IsDBNull(dr[i]["MIN_QTY"]) ? null : (Nullable<int>)(dr[i]["MIN_QTY"]));
                        info.Max_qty = (Convert.IsDBNull(dr[i]["MAX_QTY"]) ? null : (Nullable<int>)(dr[i]["MAX_QTY"]));
                        info.Image_name = (dr[i]["IMAGE_NAME"]).ToString();
                        infoSubGroup.Seq = (Convert.IsDBNull(dr[i]["ICM_RES_SUB_GRP_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_RES_SUB_GRP_SEQ"]));
                        infoUnit.Seq = (Convert.IsDBNull(dr[i]["ICM_UNIT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_UNIT_SEQ"]));
                        infoUnit.Name = (dr[i]["UNIT_NAME"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoSubGroup = infoSubGroup;
                        info.InfoUnit = infoUnit;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public ResourceMasterItemInfo GetResourceMasterItem(int seq)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            ResourceMasterItemInfo info = new ResourceMasterItemInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT i.*"
                            + ", u.ICM_UNIT_NAME AS UNIT_NAME"
                        + " FROM ICM_MAS_RESOURCE_ITEM i"
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                        + " WHERE i.ICM_RES_ITEM_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    ResourceMasterSubGroupInfo infoSubGroup = new ResourceMasterSubGroupInfo();
                    MasterUnitInfo infoUnit = new MasterUnitInfo();
                    RecordInfo infoRecord = new RecordInfo();
                    SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                    info.Seq = Convert.ToInt32(dr[0]["ICM_RES_ITEM_SEQ"]);
                    info.Code = (dr[0]["ICM_RES_ITEM_CODE"]).ToString();
                    info.Name = (dr[0]["ICM_RES_ITEM_NAME"]).ToString();
                    info.Descr = (dr[0]["ICM_RES_ITEM_DESCR"]).ToString();
                    info.Gpsc_code = (dr[0]["GPSC_CODE"]).ToString();
                    info.Giin_code = (dr[0]["GIIN_CODE"]).ToString();
                    info.Std_price = (Convert.IsDBNull(dr[0]["STD_PRICE"]) ? null : (Nullable<double>)(dr[0]["STD_PRICE"]));
                    info.Min_price = (Convert.IsDBNull(dr[0]["MIN_PRICE"]) ? null : (Nullable<double>)(dr[0]["MIN_PRICE"]));
                    info.Max_price = (Convert.IsDBNull(dr[0]["MAX_PRICE"]) ? null : (Nullable<double>)(dr[0]["MAX_PRICE"]));
                    info.Min_qty = (Convert.IsDBNull(dr[0]["MIN_QTY"]) ? null : (Nullable<int>)(dr[0]["MIN_QTY"]));
                    info.Max_qty = (Convert.IsDBNull(dr[0]["MAX_QTY"]) ? null : (Nullable<int>)(dr[0]["MAX_QTY"]));
                    infoSubGroup.Seq = (Convert.IsDBNull(dr[0]["ICM_RES_SUB_GRP_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_RES_SUB_GRP_SEQ"]));
                    infoUnit.Seq = (Convert.IsDBNull(dr[0]["ICM_UNIT_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_UNIT_SEQ"]));
                    infoUnit.Name = (dr[0]["UNIT_NAME"]).ToString();
                    info.Image_name = (dr[0]["IMAGE_NAME"]).ToString();
                    infoRecord.Created_by = (Convert.IsDBNull(dr[0]["CREATED_BY"]) ? null : (Nullable<int>)(dr[0]["CREATED_BY"]));
                    infoRecord.Created_when = (Convert.IsDBNull(dr[0]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["CREATED_WHEN"]));
                    infoRecord.Updated_by = (Convert.IsDBNull(dr[0]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[0]["UPDATED_BY"]));
                    infoRecord.Updated_when = (Convert.IsDBNull(dr[0]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["UPDATED_WHEN"]));
                    infoRecord.Restored_by = (Convert.IsDBNull(dr[0]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[0]["RESTORED_BY"]));
                    infoRecord.Restored_when = (Convert.IsDBNull(dr[0]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["RESTORED_WHEN"]));
                    infoStatus.Status = (dr[0]["RECORD_STATUS"]).ToString();
                    infoRecord.InfoStatus = infoStatus;
                    info.InfoSubGroup = infoSubGroup;
                    info.InfoUnit = infoUnit;
                    info.InfoRecord = infoRecord;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<ResourceMasterItemInfo> GetResourceMasterItemWithParent(int sub_group_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<ResourceMasterItemInfo> infoList = new List<ResourceMasterItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@sub_group_seq", sub_group_seq);

                strSQL = "SELECT i.*"
                            + ", u.ICM_UNIT_NAME AS UNIT_NAME"
                        + " FROM ICM_MAS_RESOURCE_ITEM i"
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                        + " WHERE i.RECORD_STATUS <> @delete_status"
                            + " AND i.ICM_RES_SUB_GRP_SEQ = @sub_group_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        ResourceMasterItemInfo info = new ResourceMasterItemInfo();
                        ResourceMasterSubGroupInfo infoSubGroup = new ResourceMasterSubGroupInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_RES_ITEM_SEQ"]);
                        info.Code = (dr[i]["ICM_RES_ITEM_CODE"]).ToString();
                        info.Name = (dr[i]["ICM_RES_ITEM_NAME"]).ToString();
                        info.Descr = (dr[i]["ICM_RES_ITEM_DESCR"]).ToString();
                        info.Gpsc_code = (dr[i]["GPSC_CODE"]).ToString();
                        info.Giin_code = (dr[i]["GIIN_CODE"]).ToString();
                        info.Std_price = (Convert.IsDBNull(dr[i]["STD_PRICE"]) ? null : (Nullable<double>)(dr[i]["STD_PRICE"]));
                        info.Min_price = (Convert.IsDBNull(dr[i]["MIN_PRICE"]) ? null : (Nullable<double>)(dr[i]["MIN_PRICE"]));
                        info.Max_price = (Convert.IsDBNull(dr[i]["MAX_PRICE"]) ? null : (Nullable<double>)(dr[i]["MAX_PRICE"]));
                        info.Min_qty = (Convert.IsDBNull(dr[i]["MIN_QTY"]) ? null : (Nullable<int>)(dr[i]["MIN_QTY"]));
                        info.Max_qty = (Convert.IsDBNull(dr[i]["MAX_QTY"]) ? null : (Nullable<int>)(dr[i]["MAX_QTY"]));
                        info.Image_name = (dr[i]["IMAGE_NAME"]).ToString();
                        infoSubGroup.Seq = (Convert.IsDBNull(dr[i]["ICM_RES_SUB_GRP_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_RES_SUB_GRP_SEQ"]));
                        infoUnit.Seq = (Convert.IsDBNull(dr[i]["ICM_UNIT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_UNIT_SEQ"]));
                        infoUnit.Name = (dr[i]["UNIT_NAME"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoSubGroup = infoSubGroup;
                        info.InfoUnit = infoUnit;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<ResourceMasterItemInfo> GetResourceMasterItem(Nullable<int> Sequence, Nullable<int> ResourceType, string SearchText)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<ResourceMasterItemInfo> infoList = new List<ResourceMasterItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@ResourceType", ResourceType);
                dbManager.AddParameters(2, "@search_text", SearchText);
                dbManager.AddParameters(3, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT ri.*"
                            + ", g.ICM_RES_GRP_NAME, sg.ICM_RES_SUB_GRP_NAME, u.ICM_UNIT_NAME"
                        + " FROM ICM_MAS_RESOURCE_ITEM ri"
                            + " INNER JOIN ICM_MAS_RESOURCE_SUB_GROUP sg ON ri.ICM_RES_SUB_GRP_SEQ = sg.ICM_RES_SUB_GRP_SEQ"
                            + " INNER JOIN ICM_MAS_RESOURCE_GROUP g ON sg.ICM_RES_GRP_SEQ = g.ICM_RES_GRP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_TYPE rt ON g.ICM_RES_TYPE_SEQ = rt.ICM_RES_TYPE_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON ri.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                        + " WHERE ri.RECORD_STATUS <> @delete_status"
                        + (Sequence.HasValue ? " AND ri.ICM_RES_ITEM_SEQ = @Sequence" : "")
                        + (ResourceType.HasValue ? " AND g.ICM_RES_TYPE_SEQ = @ResourceType" : "")
                            + " AND (g.ICM_RES_TYPE_SEQ = 1 OR g.ICM_RES_TYPE_SEQ = 2)"
                            + " AND CASE WHEN @search_text = '' THEN 0 = 0"
                                + " ELSE (ri.ICM_RES_ITEM_DESCR LIKE CONCAT('%', @search_text , '%')"
                                    + " OR g.ICM_RES_GRP_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " OR sg.ICM_RES_SUB_GRP_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " OR ri.ICM_RES_ITEM_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " OR u.ICM_UNIT_NAME LIKE CONCAT('%', @search_text , '%')"
                                + ")"
                            + " END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        ResourceMasterItemInfo info = new ResourceMasterItemInfo();
                        ResourceMasterSubGroupInfo infoSubGroup = new ResourceMasterSubGroupInfo();
                        ResourceMasterGroupInfo infoGroup = new ResourceMasterGroupInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[0]["ICM_RES_ITEM_SEQ"]);
                        info.Code = (dr[0]["ICM_RES_ITEM_CODE"]).ToString();
                        info.Name = (dr[0]["ICM_RES_ITEM_NAME"]).ToString();
                        info.Descr = (dr[0]["ICM_RES_ITEM_DESCR"]).ToString();
                        info.Gpsc_code = (dr[0]["GPSC_CODE"]).ToString();
                        info.Giin_code = (dr[0]["GIIN_CODE"]).ToString();
                        info.Std_price = (Convert.IsDBNull(dr[0]["STD_PRICE"]) ? null : (Nullable<double>)(dr[0]["STD_PRICE"]));
                        info.Min_price = (Convert.IsDBNull(dr[0]["MIN_PRICE"]) ? null : (Nullable<double>)(dr[0]["MIN_PRICE"]));
                        info.Max_price = (Convert.IsDBNull(dr[0]["MAX_PRICE"]) ? null : (Nullable<double>)(dr[0]["MAX_PRICE"]));
                        info.Min_qty = (Convert.IsDBNull(dr[0]["MIN_QTY"]) ? null : (Nullable<int>)(dr[0]["MIN_QTY"]));
                        info.Max_qty = (Convert.IsDBNull(dr[0]["MAX_QTY"]) ? null : (Nullable<int>)(dr[0]["MAX_QTY"]));
                        infoSubGroup.Seq = (Convert.IsDBNull(dr[0]["ICM_RES_SUB_GRP_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_RES_SUB_GRP_SEQ"]));
                        infoUnit.Seq = (Convert.IsDBNull(dr[0]["ICM_UNIT_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_UNIT_SEQ"]));
                        infoUnit.Name = (dr[0]["ICM_UNIT_NAME"]).ToString();
                        info.Image_name = (dr[0]["IMAGE_NAME"]).ToString();
                        //infoRecord.Created_by = (Convert.IsDBNull(dr[0]["CREATED_BY"]) ? null : (Nullable<int>)(dr[0]["CREATED_BY"]));
                        //infoRecord.Created_when = (Convert.IsDBNull(dr[0]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["CREATED_WHEN"]));
                        //infoRecord.Updated_by = (Convert.IsDBNull(dr[0]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[0]["UPDATED_BY"]));
                        //infoRecord.Updated_when = (Convert.IsDBNull(dr[0]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["UPDATED_WHEN"]));
                        //infoRecord.Restored_by = (Convert.IsDBNull(dr[0]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[0]["RESTORED_BY"]));
                        //infoRecord.Restored_when = (Convert.IsDBNull(dr[0]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["RESTORED_WHEN"]));
                        //infoStatus.Status = (dr[0]["RECORD_STATUS"]).ToString();
                        //infoRecord.InfoStatus = infoStatus;
                        info.InfoSubGroup = infoSubGroup;
                        info.InfoUnit = infoUnit;
                        //info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void Insert(ResourceMasterItemInfo info)
        {
            string strSQL;
            DataSet ds;
            string strLastId = "";
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(17);

                dbManager.AddParameters(0, "@code", info.Code);
                dbManager.AddParameters(1, "@name", info.Name);
                dbManager.AddParameters(2, "@descr", info.Descr);
                dbManager.AddParameters(3, "@sub_group_seq", info.InfoSubGroup.Seq);
                dbManager.AddParameters(4, "@gpsc_code", info.Gpsc_code);
                dbManager.AddParameters(5, "@giin_code", info.Giin_code);
                dbManager.AddParameters(6, "@std_price", info.Std_price);
                dbManager.AddParameters(7, "@min_price", info.Min_price);
                dbManager.AddParameters(8, "@max_price", info.Max_price);
                dbManager.AddParameters(9, "@unit_seq", info.InfoUnit.Seq);
                dbManager.AddParameters(10, "@min_qty", info.Min_qty);
                dbManager.AddParameters(11, "@max_qty", info.Max_qty);
                dbManager.AddParameters(12, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(13, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(14, "@created_when", info.InfoRecord.Created_when);
                dbManager.AddParameters(15, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(16, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "INSERT INTO ICM_MAS_RESOURCE_ITEM"
                    + " (ICM_RES_ITEM_CODE, ICM_RES_ITEM_NAME, ICM_RES_ITEM_DESCR"
                    + " , ICM_RES_SUB_GRP_SEQ, GPSC_CODE, GIIN_CODE, STD_PRICE, MIN_PRICE, MAX_PRICE"
                    + " , ICM_UNIT_SEQ, MIN_QTY, MAX_QTY"
                    + " , RECORD_STATUS, CREATED_BY, CREATED_WHEN, UPDATED_BY, UPDATED_WHEN)"
                    + " VALUES(@code"
                    + ", @name"
                    + ", @descr"
                    + ", @sub_group_seq"
                    + ", @gpsc_code"
                    + ", @giin_code"
                    + ", @std_price"
                    + ", @min_price"
                    + ", @max_price"
                    + ", @unit_seq"
                    + ", @min_qty"
                    + ", @max_qty"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + ", @updated_by"
                    + ", @updated_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "SELECT LAST_INSERT_ID()"
                    + " FROM ICM_MAS_RESOURCE_ITEM";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    strLastId = ds.Tables[0].Rows[0][0].ToString();
                }
                if (info.InfoImage.File_info != null)
                {
                    UploadFile(info.InfoImage, strLastId);
                    //UploadFileToServer(info.InfoImage, strLastId);

                    dbManager.CreateParameters(2);
                    dbManager.AddParameters(0, "@seq", Convert.ToInt32(strLastId));
                    dbManager.AddParameters(1, "@image_name", strLastId + info.InfoImage.File_info.Extension);

                    strSQL = "UPDATE ICM_MAS_RESOURCE_ITEM"
                        + " SET IMAGE_NAME = @image_name"
                        + " WHERE ICM_RES_ITEM_SEQ = @seq";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(ResourceMasterItemInfo info, string change_file)
        {
            string strSQL;
            DataSet ds;
            string strOldImage = "";
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@seq", info.Seq);

                strSQL = "SELECT IMAGE_NAME"
                    + " FROM ICM_MAS_RESOURCE_ITEM"
                    + " WHERE ICM_RES_ITEM_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    strOldImage = ds.Tables[0].Rows[0][0].ToString();
                }

                dbManager.CreateParameters(19);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@code", info.Code);
                dbManager.AddParameters(2, "@name", info.Name);
                dbManager.AddParameters(3, "@descr", info.Descr);
                dbManager.AddParameters(4, "@sub_group_seq", info.InfoSubGroup.Seq);
                dbManager.AddParameters(5, "@gpsc_code", info.Gpsc_code);
                dbManager.AddParameters(6, "@giin_code", info.Giin_code);
                dbManager.AddParameters(7, "@std_price", info.Std_price);
                dbManager.AddParameters(8, "@min_price", info.Min_price);
                dbManager.AddParameters(9, "@max_price", info.Max_price);
                dbManager.AddParameters(10, "@unit_seq", info.InfoUnit.Seq);
                dbManager.AddParameters(11, "@min_qty", info.Min_qty);
                dbManager.AddParameters(12, "@max_qty", info.Max_qty);
                dbManager.AddParameters(13, "@image_name", info.Image_name);
                dbManager.AddParameters(14, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(15, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(16, "@created_when", info.InfoRecord.Created_when);
                dbManager.AddParameters(17, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(18, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_MAS_RESOURCE_ITEM"
                    + " SET ICM_RES_ITEM_CODE = @code"
                    + ", ICM_RES_ITEM_NAME = @name"
                    + ", ICM_RES_ITEM_DESCR = @descr"
                    + ", ICM_RES_SUB_GRP_SEQ = @sub_group_seq"
                    + ", GPSC_CODE = @gpsc_code"
                    + ", GIIN_CODE = @giin_code"
                    + ", STD_PRICE = @std_price"
                    + ", MIN_PRICE = @min_price"
                    + ", MAX_PRICE = @max_price"
                    + ", ICM_UNIT_SEQ = @unit_seq"
                    + ", MIN_QTY = @min_qty"
                    + ", MAX_QTY = @max_qty"
                    + (change_file.Length > 0 ? ", IMAGE_NAME = @image_name" : "")
                    + ", RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_RES_ITEM_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                if (change_file.Length > 0)
                {
                    if (strOldImage.Length > 0)
                    {
                        if (File.Exists(info.InfoImage.Dest_path + strOldImage))
                        {
                            File.Delete(info.InfoImage.Dest_path + strOldImage);
                        }
                    }

                    if (info.InfoImage.File_info != null)
                    {
                        UploadFile(info.InfoImage, info.Seq.ToString());
                    }
                }

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(ResourceMasterItemInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(3, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_MAS_RESOURCE_ITEM"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_RES_ITEM_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteAll(IList<ResourceMasterItemInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(2, "@updated_when", infoList[0].InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_MAS_RESOURCE_ITEM"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_RES_ITEM_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UploadFile(ImageInfo image_info, string image_id)
        {
            if (Convert.ToInt64(image_info.File_info.Length) > 0)
            {
                if( !Directory.Exists(image_info.Dest_path) )
                {
				    Directory.CreateDirectory(image_info.Dest_path);
                }

                if (File.Exists(image_info.Dest_path + image_id + image_info.File_info.Extension))
                {
                    File.Delete(image_info.Dest_path + image_id + image_info.File_info.Extension);
                }

                File.Copy(image_info.File_info.FullName, image_info.Dest_path + image_id + image_info.File_info.Extension);
            }
        }

        public void UploadFileToServer(ImageInfo image_info, string image_id)
        {
            string ftpServer = @"ftp://g-erp-web/APP_UI/Image/ItemPicture";
            string userName = "Administrator";
            string password = "P@ssw0rd";
            using (System.Net.WebClient client = new System.Net.WebClient())
            {
                client.Credentials = new System.Net.NetworkCredential(userName, password);
                client.UploadFile(ftpServer + "/" + image_id + image_info.File_info.Extension, image_info.File_info.FullName);
            }
        }

        public void DeleteFromDB(IList<ResourceMasterItemInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM ICM_MAS_RESOURCE_ITEM"
                    + " WHERE ICM_RES_ITEM_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Restore(IList<ResourceMasterItemInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(1, "@restored_by", infoList[0].InfoRecord.Restored_by);
                dbManager.AddParameters(2, "@restored_when", infoList[0].InfoRecord.Restored_when);

                strSQL = "UPDATE ICM_MAS_RESOURCE_ITEM"
                    + " SET RECORD_STATUS = @record_status"
                    + ", RESTORED_BY = @restored_by"
                    + ", RESTORED_WHEN = @restored_when"
                    + " WHERE ICM_RES_ITEM_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
